#!/usr/bin/perl -w
use strict;
use DBI;
# Jason Weirather 2010.
#this one will process each hit sequentially from the text file
#that is output by print_matches.pl
#so format is 
#genome contig start stop string
# it will calculate conservation information for every hit,
# then it will add this conservation information to the database.
my $username = 'x';
my $tablebasename = '';
#automatically set tablebasename;
if(scalar(@ARGV) < 1) { die "tell it the regex_id\n"; }
my ($regex_id) = @ARGV;
my $zdatabase = "zebraphish";
my $platform = "mysql";
my $user = $username;
my $zdbh = DBI->connect("dbi:$platform:$zdatabase",$user,'');
my $zquery = "SELECT completed, species FROM regex_scan WHERE db_id = $regex_id LIMIT 1";
print "$zquery\n";
my $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
my $count=0;
my $species;
while(my $row = $zst->fetchrow_hashref()) {
  if($row->{'completed'} == 0) { die "the search for this sequence is incomplete.\n"; }
  $species = $row->{'species'};
}
# if we are still here, then we haven't finished this yet.

my @tables;
if($species eq 'danrer4') { @tables = ('_vertebrate'); }
if($species eq 'fr2') { @tables = ('_vertebrate'); }
if($species eq 'hg18') { @tables = ('_vertebrate','_placental'); }
if($species eq 'mm9') { @tables = ('_vertebrate','_placental'); }

#we are running through it so make sure we are not overwritting first.
$zquery = "DELETE FROM hit_conservation WHERE conservation_scan_id IN (SELECT conservation_scan.db_id FROM conservation_scan WHERE regex_id = " . $regex_id . ")";
print "$zquery\n";
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
$zquery = "DELETE FROM conservation_scan WHERE regex_id = " . $regex_id;
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
print "$zquery\n";
$zst->execute() or die "The execution of query field: $DBI::errstr";

# get the chromosomes
$zquery = "SELECT chromosome FROM chromosome_scan JOIN regex_scan ON regex_id = regex_scan.db_id AND regex_scan.db_id = " . $regex_id;
$zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
$zst->execute() or die "The execution of query field: $DBI::errstr";
my @chromosomes;
while(my $row = $zst->fetchrow_hashref()) {
  push @chromosomes, $row->{'chromosome'};
}
my @database_numbers;
foreach my $table (@tables) { 
 foreach my $chromosome (@chromosomes) {
  my @conservation_levels = ('05_1','4_5','8_9');
  foreach my $conservation_level (@conservation_levels) {
    my $cons = $conservation_level;
    $conservation_level = "_" . $conservation_level;
    my $name = $species . $table . $conservation_level;
    $zquery = "SELECT hit_start, hit_finish, hits.db_id FROM hits JOIN chromosome_scan ON chromosome_scan_id = chromosome_scan.db_id JOIN regex_scan ON regex_id = regex_scan.db_id AND chromosome = '$chromosome' AND regex_scan.db_id = " . $regex_id . " ORDER BY hit_start";
    print "$zquery\n";
    $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
    $zst->execute() or die "The execution of query field: $DBI::errstr";
    my @hit_starts;
    my @hit_finishes;
    my @hit_ids;
    my $min_hit_start = 1000000000;
    my $max_hit_finish = -1; 
    while(my $row = $zst->fetchrow_hashref()) {
      if($row->{'hit_start'} < $min_hit_start) { $min_hit_start = $row->{'hit_start'}; }
      if($row->{'hit_finish'} > $max_hit_finish) { $max_hit_finish = $row->{'hit_finish'}; }
      push @hit_starts, $row->{'hit_start'};
      push @hit_finishes, $row->{'hit_finish'};
      push @hit_ids, $row->{'db_id'};
    }
    $zquery = "SELECT start, finish FROM conservation.$name WHERE chromosome = '$chromosome' AND (finish >= $min_hit_start AND start <= $max_hit_finish) ORDER BY start";
    print "$zquery\n";
    $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
    $zst->execute() or die "The execution of query field: $DBI::errstr";
    my @starts;
    my @finishes;
    while(my $row = $zst->fetchrow_hashref()) {
      push @starts, $row->{'start'};
      push @finishes, $row->{'finish'};
    }
    #now we need to process all this phastcon data
    my @conserved_ids = process_phastcons(\@hit_starts, \@hit_finishes, \@hit_ids,\@starts, \@finishes);
    foreach my $id (@conserved_ids) { push @database_numbers, "$id\t$cons\t$table"; } 
  }
 }
}
#### finished 
print "\nfinishing inserts please wait\n";
foreach my $table (@tables) {
  $zquery = "INSERT INTO conservation_scan (regex_id,conservation_database,species,completed) VALUES ('$regex_id', '$table', '$species',1)";

  #print "$zquery\n";
  $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
}  

foreach my $id (@database_numbers) {
  print "$id\n";
  my ($hit,$cons,$table) = split(/\t/,$id);
  #look up the conservation_scan id
  $zquery = "SELECT db_id FROM conservation_scan WHERE conservation_database = '$table' AND  regex_id = '$regex_id' LIMIT 1";
  print "$zquery\n";
  $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
  my $curreg = 0;
  while(my $row = $zst->fetchrow_hashref()) {
    $curreg = $row->{'db_id'};
  }
  
  $zquery = "INSERT INTO hit_conservation (hit_id,conservation_score,conservation_scan_id) VALUES ($hit,'$cons',$curreg)";
  print "$zquery\n";
  $zst = $zdbh->prepare($zquery) or die "preparing query failed: $DBI::errstr";
  $zst->execute() or die "The execution of query field: $DBI::errstr";
}

sub process_phastcons {
  my @hit_starts = @{shift @_};
  my @hit_finishes = @{shift @_};
  my @hit_ids = @{shift @_};
  my @starts = @{shift @_};
  my @finishes = @{shift @_};
  my $conservation_index = 0;
  my @conserved_ids;
  #print "checking " . scalar(@hit_ids) . " hit ids\n";
  #print "checking against " . scalar(@starts) . " starts\n";
  for(my $i = 0; $i < scalar(@hit_ids); $i++) {
    my $hit = 'false';
    my $j = $conservation_index;
    my $too_far = 'false';
    #print "$j\n";
    while($j < scalar(@starts) && $hit eq 'false') {
      if($hit_starts[$i] > $finishes[$j]) { $conservation_index = $j; }
      if($starts[$j] > $hit_finishes[$i]) { $hit = 'skip'; }
      #print "trying $starts[$j] $finishes[$j] $hit_starts[$i] $hit_finishes[$i]\n";
      if($hit_starts[$i] >= $starts[$j] && $hit_finishes[$i] <= $finishes[$j]) {
        #print "true\n";
        $hit = 'true';
      }
      if($hit_starts[$i] <= $starts[$j] && $hit_finishes[$i] >= $starts[$j]) {
        #print "true\n";
        $hit = 'true';
      }
      if($hit_starts[$i] <= $finishes[$j] && $hit_finishes[$i] >= $finishes[$j]) {
        #print "true\n";
        $hit = 'true';
      }
      if($hit_starts[$i] <= $starts[$j] && $hit_finishes[$i] >= $finishes[$j]) {
        #print "true\n";
        $hit = 'true';
      }
      $j++;
      if($hit eq 'true') { push @conserved_ids, $hit_ids[$i]; }
    }
  }
  #print scalar(@conserved_ids) . " conserved ids\n";
  return @conserved_ids;
}

